package org.phalanx.server.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import org.phalanx.server.ServerCache.QueryObject;
import org.phalanx.server.properties.ServerPreferences;

/*
 * Author: Steven Day
 * This class keeps a persistence connection up for SQL connections
 * each instance of a class opens up a unique connection.
 * 
 * Values are read in from server config classes
 * 
 * We will first focus on a mySQL oriented approach
 * 
 * */
public class SQLManager {
	private ServerPreferences pref;
	private String sqlHostname; 
	private int hostPort;
	private String databaseName;
	private String username;
	private String password;
	private Connection persistenceConnection;
	private String driverName;
	private int disconnectCount = 0;
	private static final int maxDisconnects = 40;
		
	
	public SQLManager(ServerPreferences pref){
		this.pref = pref;
		
		if (pref != null){
			populatePreferenceValues();
		}
		
	}
	
	private void populatePreferenceValues(){
		sqlHostname = pref.getProperties("PhalanxServer.Persistence.Source.ServerHost");
		hostPort = pref.getPropertiesAsInt("PhalanxServer.Persistence.Source.ServerPort",
				new Integer(3306)).intValue();
		databaseName = pref.getProperties("PhalanxServer.Persistence.Source.Database", "Phalanx");
		username = pref.getProperties("PhalanxServer.Persistence.Source.Username");
		password = pref.getProperties("PhalanxServer.Persistence.Source.Password");
	}
	
	public boolean connect() throws SQLException {
		boolean isSuccess = false;
		String jdbcURL = "jdbc:";
		if (!this.isConnected()){
			try {
				if (persistenceConnection != null){
					persistenceConnection.close();
				}
					
				this.driverName = initializeDriver();

				jdbcURL += driverName + "://" + sqlHostname + ":" + hostPort + "/" + databaseName;
				persistenceConnection = DriverManager.getConnection(jdbcURL, username,password);
				
				isSuccess = true;
			} catch (ClassNotFoundException cnf){
				cnf.printStackTrace();
			} catch (SQLException se){
				if (disconnectCount <= 0){
					throw se;
				}
					
			}
		}

		return isSuccess;
	}
	
	public boolean disconnect() throws SQLException {
		boolean isSuccess = false;
		if (!persistenceConnection.isClosed()){		
			persistenceConnection.close();
			isSuccess = true;
		}
		
		return isSuccess;
	}
	
	public boolean isConnected(){
		boolean isConnected = false;

		if (persistenceConnection != null){
			try {
				isConnected = !persistenceConnection.isClosed();
			} catch (SQLException e){}
		}
		return isConnected;
	}
	
	private String initializeDriver() throws ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		return "mysql";
	}
	
	synchronized public ArrayList<QueryObject> getQuery(String sqlStatement, QueryObject template) throws SQLException{
		ArrayList<QueryObject> queryList = new ArrayList<QueryObject>();
		ArrayList<String> columnNames = template.getColumns();
		Statement sqlStatementObject;
		ResultSet rs;

		try {
			sqlStatementObject = persistenceConnection.createStatement();
			rs = sqlStatementObject.executeQuery(sqlStatement);

			while(rs.next()){
				QueryObject tempRetrieve = new QueryObject();

				for (int i = 0; i < columnNames.size(); i++){
					String currentColumn = columnNames.get(i);
					tempRetrieve.setString(currentColumn, rs.getString(currentColumn));
				}

				queryList.add(tempRetrieve);
			}

			sqlStatementObject.close();
		} catch (SQLException se){
			se.printStackTrace();
			disconnectCount++;

			if (!this.isConnected()){
				this.connect();
				if (disconnectCount > maxDisconnects){
					throw se;
				}
				Sleep(2000);
				queryList = getQuery(sqlStatement, template);
				disconnectCount = 0;
			}
		}
		return queryList;
	}
	
	private void Sleep(int sec){
		try {
			Thread.sleep(sec);
		} catch (InterruptedException ie){}
	}
	
	synchronized public boolean insertQuery(String sqlStatement) throws SQLException{
		boolean isSuccess = false;
		Statement sqlStatementObject;

		try {
			sqlStatementObject = persistenceConnection.createStatement();

			if (sqlStatementObject.executeUpdate(sqlStatement) == 1){
				isSuccess = true;
			}
		} catch (SQLException se){
			disconnectCount++;

			if (!this.isConnected()){
				this.connect();
				if (disconnectCount > maxDisconnects){
					throw se;
				}
				Sleep(2000); // Pause 2 seconds between 
				isSuccess = insertQuery(sqlStatement);
				disconnectCount = 0;
			}
		}
		
		return isSuccess;
	}
	
}
